﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.IO;
using System.Web;
using FrameworkManapro.Utilities;
using FrameworkManapro.BusinessModel;
using GMAC.ReportingSystem.Business.Entities;
using GMAC.ReportingSystem.Business.Entities.Configuration;

namespace GMAC.ReportingSystem.DataAccess
{
    public class ASLTelecomAccess
    {


        public void GenerateUploadFile(int UserId)
        {
            ArrayList parameters = new ArrayList { UserId };
            DataAccessComponent.ExecuteOperation("procASLTelecomMerge", parameters);
        } // GenerateUploadFile


        public StringASLTelecomCollection GetASLTelecoms(LoadFileStatus status, DateTime processDate, int user)
        {
            StringASLTelecomCollection Records = new StringASLTelecomCollection();
            StringASLTelecom Record;
            ArrayList parameters = new ArrayList { status, processDate, user };
            DataTable dt = DataAccessComponent.ExecuteSelect("procTemporaryASLTelecomByFilter", parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    Record = new StringASLTelecom
                    {
                        LoadDate = (DateTime)row["LoadDate"],
                        dDate = row["dDate"].ToString(),
                        MessageType = row["MessageType"].ToString(),
                        RecordsReceived = row["RecordsReceived"].ToString(),
                        RecordsSent = row["RecordsSent"].ToString(),
                        RecordsNotLoaded = row["RecordsNotLoaded"].ToString(),
                        RejectionReason = row["RejectionReason"].ToString()

                    }; // Record = new StringASLTelecom

                    Records.Add(Record);
                } // foreach
            } // if
            return Records;
        } // GetASLTelecoms

        /// <summary>
        /// Inserta en una tabla temporal los registros del archivo excel
        /// </summary>
        /// <param name="processDate">Fecha en la que se esta procesando el archivo</param>
        /// <param name="UserId">Codigo del usuario que esta procesando el archivo</param>
        /// <param name="Records">Coleccion de registros a insertar en la tabla temporal</param>
        public void UploadLoanFile(DateTime processDate, int UserId, StringASLTelecomCollection Records, ref StringASLTelecomCollection ASLInvalids, ref StringASLTelecomCollection ASLValids)
        {
            try
            {
                DataRow row;
                DataTable dt = new DataTable();

                dt.Columns.Add("LoadDate"           , typeof(DateTime));
                dt.Columns.Add("dDate"              , typeof(string));
                dt.Columns.Add("MessageType"        , typeof(string));
                dt.Columns.Add("RecordsReceived"    , typeof(string));
                dt.Columns.Add("RecordsSent"        , typeof(string));
                dt.Columns.Add("RecordsNotLoaded"   , typeof(string));
                dt.Columns.Add("UserID"             , typeof(string));

                foreach (StringASLTelecom Record in Records)
                {
                    row = dt.NewRow();

                    row["LoadDate"]          =    Record.LoadDate;
                    row["dDate"]             =    Record.dDate ;
                    row["MessageType"]       =    Record.MessageType;
                    row["RecordsReceived"]   =    Record.RecordsReceived;
                    row["RecordsSent"]       =    Record.RecordsSent;
                    row["RecordsNotLoaded"]  =    Record.RecordsNotLoaded;
                    row["UserID"]            =    UserId;

                    // Inserta el registro como Correcto 
                    // y despues se ejecuta la validacion en el SP que puede cambiar el estatus a invalido
                    //row["bitStatus"]            = OK;

                    dt.Rows.Add(row);
                } // foreach

                // asigna los parametros para llamar el SP
                var parameters = new ArrayList { processDate, UserId, dt };

                // Llama el SP que inserta en la tabla temporal
                DataAccessComponent.ExecuteOperation("procTemporaryASLTelecomInsert", parameters);

                // Llama los metodos que obtiene la lista de registros validos e invalidos para retornarlos en la funcion
                ASLInvalids = GetASLTelecoms(LoadFileStatus.Invalid, processDate, UserId);
                ASLValids   = GetASLTelecoms(LoadFileStatus.Valid, processDate, UserId);
            } // try
            catch (Exception)
            {
                throw;
            } // catch


        } // UploadLoanFile


        /// <summary>
        /// Recibe una coleccion y la retorna ordenada segun indiquen los parametros
        /// </summary>
        /// <param name="records">Coleccion de valores a ordenar</param>
        /// <param name="column">Campo por el que va a ordenar</param>
        /// <param name="order">"ASC" o "DESC"</param>
        /// <returns>Lista ordenada</returns>
        public StringASLTelecomCollection sord(StringASLTelecomCollection records, string column, string order)
        {
            StringASLTelecomCollection Result = new StringASLTelecomCollection();
            StringASLTelecom NewRecord;
            DataRow row;
            DataTable dt = new DataTable();            

            dt.Columns.Add("LoadDate", typeof(DateTime));
            dt.Columns.Add("dDate", typeof(string));
            dt.Columns.Add("MessageType", typeof(string));
            dt.Columns.Add("RecordsReceived", typeof(string));
            dt.Columns.Add("RecordsSent", typeof(string));
            dt.Columns.Add("RecordsNotLoaded", typeof(string));
            dt.Columns.Add("UserID", typeof(string));
            dt.Columns.Add("RejectionReason", typeof(string));
            dt.Columns.Add("RejectionReason_Small", typeof(string));

            // Captura todos los registros de la coleccion en la tabla para ordenarlos
            foreach (StringASLTelecom Record in records)
            {
                row = dt.NewRow();

                row["LoadDate"] = Record.LoadDate;
                row["dDate"] = Record.dDate;
                row["MessageType"] = Record.MessageType;
                row["RecordsReceived"] = Record.RecordsReceived;
                row["RecordsSent"] = Record.RecordsSent;
                row["RecordsNotLoaded"] = Record.RecordsNotLoaded;
                row["UserID"] = Record.UserID;
                row["RejectionReason"] = Record.RejectionReason;
                row["RejectionReason_Small"] = Record.RejectionReason_Small;

                dt.Rows.Add(row);
            } // foreach


            // Ordena los valores
            dt.DefaultView.Sort = column + " " + order;

            dt = dt.DefaultView.ToTable();

            // vuelve a asignar los registros en la coleccion con el nuevo orden
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow newrow in dt.Rows)
                {
                    NewRecord = new StringASLTelecom
                    {
                        LoadDate = (DateTime)newrow["LoadDate"],
                        dDate = newrow["dDate"].ToString(),
                        MessageType = newrow["MessageType"].ToString(),
                        RecordsReceived = newrow["RecordsReceived"].ToString(),
                        RecordsSent = newrow["RecordsSent"].ToString(),
                        RecordsNotLoaded = newrow["RecordsNotLoaded"].ToString(),
                        //UserID = (int)newrow["UserID"],
                        RejectionReason = newrow["RejectionReason"].ToString()

                    }; // Record = new StringAdditionalInformationDealer

                    Result.Add(NewRecord);
                } // foreach
            } // if

            // Retorna los valores en la coleccion ordenados
            return Result;

        } // sord


    } // class
} // namespace
